cls
clear all
set matsize 11000

********************************************************************************
	
	qui import delimited "raw/tradecosts/keep_fips.csv"
	compress
	qui save "temp/keep_fips.dta", replace
	
********************************************************************************
****** Load Distance and Travel Time Data  *************************************
********************************************************************************
	
	** Load US county to US port centroid files
	
		*->US county
		
			qui import delimited "raw/tradecosts/centroids_new.csv", clear
			keeporder objectid geoid name
			qui replace objectid = _n
			rename (geoid name) (fips countyname)
			compress
			qui save "temp/county_temp.dta", replace

	** Actual, 2010-1980
	
		*|1980-2010
		foreach year in "2000" "1990" "1980" {
		
		*|cost1, cost2
		forvalues cost = 1/1 {
			
			qui import delimited "raw/tradecosts/cost`cost'_`year'.txt", clear  varn(1)
			keeporder name originid destinationid total_cost`cost' total_distance
			qui capture replace originid = subinstr(originid,",","",.) 
			qui capture replace destinationid = subinstr(destinationid,",","",.) 
			qui capture replace total_distance = subinstr(total_distance,",","",.) 
			qui capture destring originid destinationid total_distance, replace
			
			*->merge origin
			
				rename originid objectid 
				qui joinby objectid using "temp/county_temp.dta",  unmatched(both)
				qui drop _merge
				rename (objectid fips countyname) (originid originfips origincountyname)
			
			*->merge destination
			
				rename destinationid objectid 
				qui joinby objectid using "temp/county_temp.dta", unmatched(both)
				qui drop _merge
				rename (objectid fips countyname) (destinationid destinationfips destinationcountyname)	
				
			*-> save
				
				sort originfips destinationfips
				rename (total_cost`cost' total_distance) (traveltime_`year' distance_`year')
				keeporder originfips origincountyname destinationfips destinationcountyname traveltime* distance*
				
				qui save "temp/_temp_`year'_cost`cost'.dta", replace	
			
				}
				}
	
	** Append files together
	
		*|cost1
		forvalues cost = 1/1 {

			*-> load 
			
				use "temp/_temp_2000_cost`cost'.dta", clear
				*rename (traveltime distance) (traveltime_2000 distance_2000)
				
				foreach year in "1990" "1980" {
					
					qui joinby originfips destinationfips using "temp/_temp_`year'_cost`cost'.dta", unmatched(both)
					qui drop _merge

					}
				
			*->combine counties
			
				qui replace originfips = 46113 if originfips==46102
				qui replace originfips = 51901 if originfips==51003|originfips==51540
				qui replace originfips = 51903 if originfips==51005|originfips==51580
				qui replace originfips = 51907 if originfips==51015|originfips==51790|originfips==51820
				qui replace originfips = 51909 if originfips==51019|originfips==51515
				qui replace originfips = 51911 if originfips==51031|originfips==51680
				qui replace originfips = 51913 if originfips==51035|originfips==51640
				qui replace originfips = 51918 if originfips==51053|originfips==51570|originfips==51730
				qui replace originfips = 51919 if originfips==51059|originfips==51600|originfips==51610
				qui replace originfips = 51921 if originfips==51069|originfips==51840
				qui replace originfips = 51923 if originfips==51081|originfips==51595
				qui replace originfips = 51929 if originfips==51089|originfips==51690
				qui replace originfips = 51931 if originfips==51095|originfips==51830
				qui replace originfips = 51933 if originfips==51121|originfips==51750
				qui replace originfips = 51939 if originfips==51143|originfips==51590
				qui replace originfips = 51941 if originfips==51149|originfips==51670
				qui replace originfips = 51942 if originfips==51153|originfips==51683|originfips==51685
				qui replace originfips = 51944 if originfips==51161|originfips==51775
				qui replace originfips = 51945 if originfips==51163|originfips==51530|originfips==51678
				qui replace originfips = 51947 if originfips==51165|originfips==51660
				qui replace originfips = 51949 if originfips==51175|originfips==51620
				qui replace originfips = 51951 if originfips==51177|originfips==51630
				qui replace originfips = 51953 if originfips==51191|originfips==51520
				qui replace originfips = 51955 if originfips==51195|originfips==51720
				qui replace originfips = 51958 if originfips==51199|originfips==51735	
				qui replace destinationfips = 46113 if destinationfips==46102
				qui replace destinationfips = 51901 if destinationfips==51003|destinationfips==51540
				qui replace destinationfips = 51903 if destinationfips==51005|destinationfips==51580
				qui replace destinationfips = 51907 if destinationfips==51015|destinationfips==51790|destinationfips==51820
				qui replace destinationfips = 51909 if destinationfips==51019|destinationfips==51515
				qui replace destinationfips = 51911 if destinationfips==51031|destinationfips==51680
				qui replace destinationfips = 51913 if destinationfips==51035|destinationfips==51640
				qui replace destinationfips = 51918 if destinationfips==51053|destinationfips==51570|destinationfips==51730
				qui replace destinationfips = 51919 if destinationfips==51059|destinationfips==51600|destinationfips==51610
				qui replace destinationfips = 51921 if destinationfips==51069|destinationfips==51840
				qui replace destinationfips = 51923 if destinationfips==51081|destinationfips==51595
				qui replace destinationfips = 51929 if destinationfips==51089|destinationfips==51690
				qui replace destinationfips = 51931 if destinationfips==51095|destinationfips==51830
				qui replace destinationfips = 51933 if destinationfips==51121|destinationfips==51750
				qui replace destinationfips = 51939 if destinationfips==51143|destinationfips==51590
				qui replace destinationfips = 51941 if destinationfips==51149|destinationfips==51670
				qui replace destinationfips = 51942 if destinationfips==51153|destinationfips==51683|destinationfips==51685
				qui replace destinationfips = 51944 if destinationfips==51161|destinationfips==51775
				qui replace destinationfips = 51945 if destinationfips==51163|destinationfips==51530|destinationfips==51678
				qui replace destinationfips = 51947 if destinationfips==51165|destinationfips==51660
				qui replace destinationfips = 51949 if destinationfips==51175|destinationfips==51620
				qui replace destinationfips = 51951 if destinationfips==51177|destinationfips==51630
				qui replace destinationfips = 51953 if destinationfips==51191|destinationfips==51520
				qui replace destinationfips = 51955 if destinationfips==51195|destinationfips==51720
				qui replace destinationfips = 51958 if destinationfips==51199|destinationfips==51735
				collapse (mean) traveltime_* distance_*, by(originfips destinationfips)
				
			*-> common fips
				
				rename originfips fips
				merge m:1 fips using "temp/keep_fips.dta"
				keep if _merge==3
				drop _merge
				rename fips originfips 
				rename destinationfips fips
				merge m:1 fips using "temp/keep_fips.dta"
				keep if _merge==3
				drop _merge
				rename fips destinationfips 
				
				
			*-> save
			
				rm "temp/keep_fips.dta"
				sort originfips destinationfips
				qui save "data/tradecosts/data-traveltime-distance-2000-1980-cost`cost'.dta", replace
			
		
			}
	
	** Remove "temp" files from previous step
	
		forvalues cost = 1/1 {
		foreach year in "2000" "1990" "1980" {
			
			rm "temp/_temp_`year'_cost`cost'.dta"
				
			}
			}
		rm "temp/county_temp.dta"
	
	** Export Travel Time and Distance to Matlab
		
		use "data/tradecosts/data-traveltime-distance-2000-1980-cost1.dta",clear	
		rename 	(traveltime_2000 traveltime_1990 traveltime_1980 distance_2000 distance_1990 distance_1980)  ///
				(Time2000 Time1990 Time1980 Distance2000 Distance1990 Distance1980)
		sort originfips destinationfips
		foreach var in Time2000 Time1990 Time1980 Distance2000 Distance1990 Distance1980 {
			sort originfips destinationfips
			qui export delimited `var' using "data/tradecosts/`var'.csv", novarnames replace
			}

	** Export Fips Codes
		
		use "data/tradecosts/data-traveltime-distance-2000-1980-cost1.dta",clear	
		keep originfips
		qui duplicates drop originfips,force
		sort originfips
		qui export delimited originfips using "data/tradecosts/Fips.csv", novarnames replace
